--------------------------------------------------------
--  DDL for View CON_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "CON_ETC_SUM_VW" ("CON_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) CON_ETC_SUM,EFIS_ID
        FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '3'
         AND SD <> '59' GROUP BY EFIS_ID;
		 
--------------------------------------------------------
--  DDL for View DES_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "DES_ETC_SUM_VW" ("DES_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) DES_ETC_SUM,EFIS_ID
        FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '1'
         AND SD <> '59' GROUP BY EFIS_ID;--------------------------------------------------------
--  DDL for View STD_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "STD_ETC_SUM_VW" ("STD_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) STD_ETC_SUM,EFIS_ID
        FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '1'
         AND SD = '59' GROUP BY EFIS_ID;
--------------------------------------------------------
--  DDL for View STE_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "STE_ETC_SUM_VW" ("STE_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) STE_ETC_SUM,EFIS_ID
       FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
      WHERE PHASE = '0'
        AND SD = '59' GROUP BY EFIS_ID;
--------------------------------------------------------
--  DDL for View STR_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "STR_ETC_SUM_VW" ("STR_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) STR_ETC_SUM,EFIS_ID
        FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '2'
         AND SD = '59' GROUP BY EFIS_ID;


--------------------------------------------------------
--  DDL for View ENV_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "ENV_ETC_SUM_VW" ("ENV_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) ENV_ETC_SUM, efis_id
        FROM evrs_ox.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '0'
         AND SD <> '59' 
         group by efis_id;
		 
--------------------------------------------------------
--  DDL for View RW_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "RW_ETC_SUM_VW" ("RW_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) RW_ETC_SUM,EFIS_ID
        FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '2'
         AND SD <> '59' GROUP BY EFIS_ID;
		 
--------------------------------------------------------
--  DDL for View STC_ETC_SUM_VW
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW  "STC_ETC_SUM_VW" ("STC_ETC_SUM", "EFIS_ID") AS 
  SELECT SUM(RES_EST_AMT) STC_ETC_SUM,EFIS_ID
        FROM EVRS_OX.PRSM_ASSIGNMENTS_PH_MV
       WHERE PHASE = '3'
         AND SD = '59' GROUP BY EFIS_ID;
--------------------------------------------------------
--  DDL for Materialized View PRSM_ASSINGMENT_TOTALS
--------------------------------------------------------

  CREATE MATERIALIZED VIEW  "PRSM_ASSIGNMENTS_TOTALS"  ("CON_ETC_SUM", "DES_ETC_SUM", "ENV_ETC_SUM", "RW_ETC_SUM", "STC_ETC_SUM", "STD_ETC_SUM", "STE_ETC_SUM", "STR_ETC_SUM", "EFIS_ID")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "EVRS_CIP_OX_TS" 
  BUILD IMMEDIATE
  USING INDEX 
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS  SELECT 
        COALESCE(CON.CON_ETC_SUM,0)AS CON_ETC_SUM,
        COALESCE(DES.DES_ETC_SUM,0)AS DES_ETC_SUM,
        COALESCE(ENV.ENV_ETC_SUM,0)AS ENV_ETC_SUM,
        COALESCE( RW.RW_ETC_SUM, 0)AS RW_ETC_SUM ,
        COALESCE(STC.STC_ETC_SUM,0)AS STC_ETC_SUM,
        COALESCE(STD.STD_ETC_SUM,0)AS STD_ETC_SUM,
        COALESCE(STE.STE_ETC_SUM,0)AS STE_ETC_SUM,
        COALESCE(STR.STR_ETC_SUM,0)AS STR_ETC_SUM,
        CX.EFIS_ID
FROM EVRS_OX.CIP_OX CX 
LEFT JOIN CON_ETC_SUM_VW CON ON CX. EFIS_ID =  CON.EFIS_ID 
LEFT JOIN DES_ETC_SUM_VW DES ON CX. EFIS_ID =  DES.EFIS_ID 
LEFT JOIN ENV_ETC_SUM_VW ENV ON CX. EFIS_ID =  ENV.EFIS_ID
LEFT JOIN RW_ETC_SUM_VW  RW  ON CX. EFIS_ID =  RW.EFIS_ID
LEFT JOIN STC_ETC_SUM_VW STC ON CX. EFIS_ID =  STC.EFIS_ID
LEFT JOIN STD_ETC_SUM_VW STD ON CX. EFIS_ID =  STD.EFIS_ID
LEFT JOIN STE_ETC_SUM_VW STE ON CX. EFIS_ID =  STE.EFIS_ID
LEFT JOIN STR_ETC_SUM_VW STR ON CX. EFIS_ID =  STR.EFIS_ID 
WHERE CX.EFIS_ID IS NOT NULL;
 

 